import sys
import os

# 将 backend 目录添加到 Python 路径
backend_dir = os.path.dirname(os.path.dirname(os.path.dirname(__file__)))
sys.path.insert(0, backend_dir)

import json
from db import db_connection
from db.operations import (
    BookOperations,
    MainCategoryOperations,
    SubCategoryOperations,
)
import pandas as pd


# 读取weixin books csv文件
async def insert_weixin_books():
    # 读取CSV文件
    weixin_books_df = pd.read_csv("db/data/weixin_books.csv", encoding="utf-8")
    print(f"📚 原始数据: {weixin_books_df.shape[0]} 条记录")

    # 去除评分为0的书籍
    clean_books_df = weixin_books_df[weixin_books_df["rating"] > 0].copy()
    print(f"🔧 清洗后数据: {clean_books_df.shape[0]} 条记录")
    clean_books_df.reset_index(drop=True, inplace=True)

    # 构建书籍特征
    insert_books_df = pd.DataFrame(
        {
            # 基本信息
            "book_id": range(1, len(clean_books_df) + 1),
            "title": clean_books_df["title"].fillna(""),
            "author": clean_books_df["author"].fillna(""),
            "translator": clean_books_df["translator"].fillna(""),
            "language": clean_books_df["language"].fillna(""),
            "intro": clean_books_df["intro"].fillna(""),
            "cover_url": clean_books_df["cover"].fillna(""),
            # 分类信息
            "main_category": clean_books_df["main_category"],
            "sub_category": clean_books_df["sub_category"],
            # 评分信息
            "good_rating": clean_books_df["good_rating"].fillna(0),
            "fair_rating": clean_books_df["fair_rating"].fillna(0),
            "poor_rating": clean_books_df["poor_rating"].fillna(0),
            "rating": clean_books_df["rating"].fillna(0),
            "rating_count": clean_books_df["rating_count"].fillna(0),
            # 其他信息
            "total_chapters": clean_books_df["total_chapters"].fillna(0),
            "price": clean_books_df["price"].fillna(0),
            "publish_time": clean_books_df["publish_time"].fillna(""),
            "reading_count": clean_books_df["reading_count"].fillna(0),
        }
    )
    # 去除分类信息
    insert_books = insert_books_df.drop(columns=["main_category", "sub_category"])

    main_category_list = []
    for _, row in insert_books_df.iterrows():
        book_id = row["book_id"]
        categories = json.loads(row["main_category"])
        for category in categories:
            main_category_list.append({"book_id": book_id, "main_category": category})

    sub_category_list = []
    for _, row in insert_books_df.iterrows():
        book_id = row["book_id"]
        categories = json.loads(row["sub_category"])
        for category in categories:
            if category:  # 确保分类不为空
                sub_category_list.append({"book_id": book_id, "sub_category": category})

    print(f"📖 准备插入 {len(insert_books)} 本图书")
    print(f"🏷️ 准备插入 {len(main_category_list)} 条主分类记录")
    print(f"🏷️ 准备插入 {len(sub_category_list)} 条子分类记录")
    print(insert_books.to_dict(orient="records")[:1])  # 打印前1条记录以检查数据
    # 初始化操作类
    book_operations = BookOperations()
    main_category_operations = MainCategoryOperations()
    sub_category_operations = SubCategoryOperations()

    # 批量插入图书数据
    books_inserted = await book_operations.batch_insert_books(
        books_data=insert_books.to_dict(orient="records"),
        on_duplicate="skip",
    )
    print(f"✅ 成功插入 {books_inserted} 条图书记录")

    # 批量插入主分类数据
    main_categories_inserted = (
        await main_category_operations.batch_insert_main_categories(
            categories_data=main_category_list,
            on_duplicate="skip",
        )
    )
    print(f"✅ 成功插入 {main_categories_inserted} 条主分类记录")

    # 批量插入子分类数据
    sub_categories_inserted = await sub_category_operations.batch_insert_sub_categories(
        categories_data=sub_category_list,
        on_duplicate="skip",
    )
    print(f"✅ 成功插入 {sub_categories_inserted} 条子分类记录")

    # 关闭数据库连接池
    await db_connection.close_pool()


# 运行
if __name__ == "__main__":
    import asyncio

    async def main():
        await insert_weixin_books()

    asyncio.run(main())
